﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using DevExpress.XtraReports.UI;
using BHair.bb;
using BHair.Report;

using DevExpress.XtraReports.Localization;
using Dxper.LocalizationCHS.Win;
using DevExpress.XtraGrid;

namespace BHair
{
    public partial class frmjcxfmx : WeifenLuo.WinFormsUI.Docking.DockContent
    {
        public frmjcxfmx()
        {
            InitializeComponent();
        }
        string constr = ConfigurationManager.ConnectionStrings["BHair.Properties.Settings.BHairDBConnectionString"].ConnectionString;
        private void frmjcxfmx_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“BHairDBDataSet4.jcxf”中。您可以根据需要移动或删除它。
           /* DevExpress.XtraGrid.Localization.GridResLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraGridCHS();
            DevExpress.XtraEditors.Controls.Localizer.Active = new Dxper.LocalizationCHS.Win.XtraEditorsCHS();
            DevExpress.XtraCharts.Localization.ChartResLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraChartsCHS();
            DevExpress.XtraBars.Localization.BarLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraBarsCHS();
            DevExpress.XtraLayout.Localization.LayoutLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraLayoutCHS();
            DevExpress.XtraPrinting.Localization.PreviewLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraPrintingCHS();
            DevExpress.XtraTreeList.Localization.TreeListResLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraTreeListCHS();
            DevExpress.Office.Localization.OfficeResLocalizer.Active = new Dxper.LocalizationCHS.Win.OfficeCHS();
            DevExpress.XtraReports.Localization.ReportLocalizer.Active = new Dxper.LocalizationCHS.Win.XtraReportsCHS();
            this.jcxfTableAdapter.Fill(this.BHairDBDataSet4.jcxf);
            */

            //this.reportViewer1.RefreshReport();
            selectxm();

            
        }

        private void button1_Click(object sender, EventArgs e)
        {

            if (txtcardid.Text != string.Empty && cbxm.Checked==false)            //卡号不为空&&项目选项未选择
            {
                //cardselect();

                getdatacardid();
            }
            else if(txtcardid.Text==string.Empty &&cbxm.Checked==true)            //卡号为空项目选项选择
            {
                xmidselect();
            }
            else if (txtcardid.Text != string.Empty && cbxm.Checked == true)       //卡号不为空项目选则
            {
                getxmcard();
            }
            else if (txtcardid.Text == string.Empty && cbxm.Checked == false)
            {
                getxmtime();
            }
            
        }
        public void selectxm()
        {
            string sql2 = "SELECT [iname] FROM [items] where jc=1";

            SqlConnection conn2 = new SqlConnection(constr);
            try
            {
                conn2.Open();
                SqlDataAdapter sda1 = new SqlDataAdapter(sql2, conn2);
                DataSet ds1 = new DataSet();
                sda1.Fill(ds1, "items");
                this.comboBox1.DataSource = ds1.Tables["items"];
                this.comboBox1.DisplayMember = "iname";
                conn2.Close();
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);
            }
        }
        public void xmselect() 
        {
            string xm = this.comboBox1.Text;
            string kstime = this.dtpk.Text;
            string endtime = this.dtpend.Text;
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql = "SELECT * FROM [jc]";
            SqlDataAdapter sda1 = new SqlDataAdapter(sql, conn);
            DataSet ds1 = new DataSet();
            sda1.Fill(ds1, "jc");

        }
        /*public void cardselect() 
        {
            string cardid = this.txtcardid.Text;
            string kstime = this.dtpk.Text;
            string endtime = this.dtpend.Text;
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql = "SELECT * FROM [BHairDB].[dbo].[jc]";
            SqlDataAdapter sda1 = new SqlDataAdapter(sql,conn);
            DataSet ds1 = new DataSet();
            sda1.Fill(ds1,"jc");
            string sql2 = string.Format("SELECT [id],[djbh],[xmid],[xmname],[xfcs],[riqi],[cardid] FROM [BHairDB].[dbo].[jcxf] where cardid='{0}' and '{1}'<=riqi and riqi<='{2}'",cardid,kstime,endtime);
            
            SqlDataAdapter sda2 = new SqlDataAdapter(sql2,conn);
            sda2.Fill(ds1,"jcxf");
            string sql3 =string.Format( "select jcxf.cardid as 卡号,jcxf.djbh as 单据号,jc.spname as 项目名称,jcxf.xfcs as 消费次数,jcxf.riqi as 消费日期 from jcxf join items on jcxf.xmid=items.iid where cardid='{0}' and '{1}'<=riqi and riqi<='{2}'",cardid,kstime,endtime);
            SqlDataAdapter sda3 = new SqlDataAdapter(sql3,conn);
            DataTable dt1 = new DataTable();
            sda3.Fill(dt1);
            this.dataGridView1.DataSource = dt1;
            //jcxfmx jcxf =new jcxfmx();
           // jcxf.SetDataSource(ds1);
            //this.crystalReportViewer1.ReportSource = jcxf;
            //this.crystalReportViewer1.PrintReport();   //直接打印
            //this.crystalReportViewer1.Show();
           // this.crystalReportViewer1.ExportReport();  //导出

        }
         * */
        public DataSet xmidselect() 
        {
            DataSet ds = new DataSet();
           SqlConnection conn = new SqlConnection(constr);
            /*string sql1 = "SELECT * FROM [BHairDB].[dbo].[jc]";
            SqlDataAdapter sda1 = new SqlDataAdapter(sql1,conn);
            DataSet ds1 = new DataSet();
            sda1.Fill(ds1,"jc");
            * */
            string sql2 =string.Format("SELECT [iid] FROM [items] where iname='{0}'", this.comboBox1.Text);
            //根据combox选项 查询所对应的 项目ID
            SqlConnection conn2 = new SqlConnection(constr);
            conn2.Open();
            SqlCommand comm = new SqlCommand(sql2,conn2);
            int xmid = 0;  //商品ID
            SqlDataReader sdr = comm.ExecuteReader();
            while(sdr.Read())
            {
                xmid = Int32.Parse(sdr["iid"].ToString().Trim());
            }
            //根据商品ID查询 消费单据
            /*
            string sql3 = string.Format("SELECT [id],[djbh],[xmid],[xmname],[xfcs],[riqi],[cardid] FROM [BHairDB].[dbo].[jcxf] where xmid={0} and '{1}'<=riqi and riqi<='{2}'",xmid,this.dtpk.Text,this.dtpend.Text);
            SqlDataAdapter sda2 = new SqlDataAdapter(sql3,conn);
            sda2.Fill(ds1,"jcxf");
             * */
            //为水晶报表赋值
            /*jcxfmx jcxf = new jcxfmx();
            jcxf.SetDataSource(ds1);
            this.crystalReportViewer1.ReportSource = jcxf;
             * */
            //根据商品ID查询消费单据 并为datagridview赋值
            string sql4 =string.Format("select distinct jcxf.id,jcxf.cardid as 卡号,member.mname as 姓名,jcxf.djbh as 单据号,xmname as 项目名称,jcxf.xfcs as 消费次数,jcxf.riqi as 消费日期 ,bz as 体重 ,bz1 as 备注,case(bs) when '0' then '消' when '1' then '退' when '2' then '消' end as 标识 from jcxf   join member on jcxf.cardid=member.mid where xmid='{0}' and '{1}'<=riqi and riqi<='{2}' order by jcxf.id", xmid,this.dtpk.Text,this.dtpend.Text+"23:59:59");
            SqlDataAdapter sda4 = new SqlDataAdapter(sql4,conn);
            DataTable dt1 = new DataTable();
            sda4.Fill(dt1);
            this.dataGridView1.DataSource = dt1;
            SqlConnection conn66 = new SqlConnection(constr);
            conn66.Open();
            string sql66 = string.Format("select jcxf.cardid,jcxf.djbh,xmname,jcxf.xfcs,jcxf.riqi from jcxf  join items on jcxf.xmid=items.iid where xmid='{0}' and '{1}'<=riqi and riqi<='{2}' order by jcxf.id", xmid, this.dtpk.Text, this.dtpend.Text+"23:59:59");
            SqlDataAdapter sda66 = new SqlDataAdapter(sql66,conn66);
            SqlConnection conn88 = new SqlConnection(constr);
            conn88.Open();
            string sql88 =string.Format("SELECT SUM(xfcs) FROM jcxf where xmid='{0}' and '{1}'<=riqi and riqi<='{2}'", xmid, this.dtpk.Text, this.dtpend.Text);
            SqlCommand comm88 = new SqlCommand(sql88,conn88);
            lbcs.Text = comm88.ExecuteScalar().ToString();

            
            sda66.Fill(ds,"jcxf");
            conn.Close();
            conn2.Close();
            conn66.Close();
            conn88.Close();
            return ds;
        }
        private DataSet getxmcard() 
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(constr);
            string sql2 = string.Format("SELECT iid, iname FROM items where iname='{0}'", this.comboBox1.Text);
            //根据combox选项 查询所对应的 项目ID
            SqlConnection conn2 = new SqlConnection(constr);
            conn2.Open();
            SqlCommand comm = new SqlCommand(sql2, conn2);
            int xmid = 0;  //商品ID
            SqlDataReader sdr = comm.ExecuteReader();
            while (sdr.Read())
            {
                xmid = Int32.Parse(sdr["iid"].ToString().Trim());
            }
            //MessageBox.Show(xmid.ToString());
            string sql4 = string.Format("select distinct jcxf.id,jcxf.cardid as 卡号,member.mname as 姓名,jcxf.djbh as 单据号,xmname as 项目名称,jcxf.xfcs as 消费次数,jcxf.riqi as 消费日期 ,bz as 体重  ,bz1 as 备注,case(bs) when '0' then '消' when '1' then '退' when '2' then '消' end as 标识 from jcxf  join member on jcxf.cardid=member.mid where cardid='{0}' and xmid='{1}' and '{2}'<=riqi and riqi<='{3}' order by jcxf.id", this.txtcardid.Text, xmid, this.dtpk.Text, this.dtpend.Text+"23:59:59");
            SqlDataAdapter sda4 = new SqlDataAdapter(sql4, conn);
            DataTable dt1 = new DataTable();
            sda4.Fill(dt1);
            this.dataGridView1.DataSource = dt1;
            SqlConnection conn66 = new SqlConnection(constr);
            conn66.Open();
            string sql66 = string.Format("select jcxf.cardid,jcxf.djbh,xmname,jcxf.xfcs,jcxf.riqi from jcxf  where cardid='{0}' and xmid='{0}' and '{1}'<=riqi and riqi<='{2}' order by jcxf.id", this.txtcardid.Text, xmid, this.dtpk.Text, this.dtpend.Text+"23:59:59");
            SqlDataAdapter sda66 = new SqlDataAdapter(sql66, conn66);
            sda66.Fill(ds, "jcxf");
            SqlConnection conn88 = new SqlConnection(constr);
            conn88.Open();
            string sql88 = string.Format("SELECT SUM(xfcs) FROM jcxf  where cardid='{0}' and xmid='{1}' and '{2}'<=riqi and riqi<='{3}'", this.txtcardid.Text, xmid, this.dtpk.Text, this.dtpend.Text);
            SqlCommand comm88 = new SqlCommand(sql88, conn88);
            lbcs.Text = comm88.ExecuteScalar().ToString();

            return ds;
        }
        private DataSet getxmtime() 
        {
            DataSet ds = new DataSet();
            SqlConnection conn1 = new SqlConnection(constr);
            conn1.Open();
            string sql1 = string.Format("select distinct jcxf.id,jcxf.cardid as 卡号,member.mname as 姓名,jcxf.djbh as 单据号,xmname as 项目名称,jcxf.xfcs as 消费次数,jcxf.riqi as 消费日期, bz as 体重 ,bz1 as 备注 ,case(bs) when '0' then '消' when '1' then '退' when '2' then '消' end as 标识 from jcxf   join member on jcxf.cardid=member.mid where '{0}'<=riqi and riqi<='{1}' order by jcxf.id", this.dtpk.Text,this.dtpend.Text+"23:59:59");
            SqlDataAdapter sda1 = new SqlDataAdapter(sql1,conn1);
            DataTable dt = new DataTable();
            sda1.Fill(dt);
            dataGridView1.DataSource = dt;
            string sql2 = string.Format("select jcxf.cardid,jcxf.djbh,xmname,jcxf.xfcs,jcxf.riqi from jcxf   where '{0}'<=riqi and riqi<='{1}' order by jcxf.id", this.dtpk.Text, this.dtpend.Text+"23:59:59");
            SqlConnection conn2 = new SqlConnection(constr);
            conn2.Open();
            SqlDataAdapter sda2 = new SqlDataAdapter(sql2,conn2);
            sda2.Fill(ds,"jcxf");
            conn1.Close();
            conn2.Close();
            
            return ds;
        }

        private void button3_Click(object sender, EventArgs e)
        {

            //this.crystalReportViewer1.ExportReport();
        }
        private DataSet getdatacardid() 
        {
            DataSet ds = new DataSet();
               string cardid = this.txtcardid.Text;
                string kstime = this.dtpk.Text;
                string endtime = this.dtpend.Text;
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();
               
                string sql3 = string.Format("select jcxf.cardid,jcxf.djbh,xmname,jcxf.xfcs,jcxf.riqi  from jcxf  where cardid='{0}' and '{1}'<=riqi and riqi<='{2}' order by jcxf.id", cardid, kstime, endtime);
                SqlDataAdapter sda3 = new SqlDataAdapter(sql3, conn);
               // DataTable dt1 = new DataTable();
                sda3.Fill(ds,"jcxf");
                
                SqlConnection conn1 = new SqlConnection(constr);
                conn1.Open();
                string sql4 = string.Format("select distinct jcxf.id,jcxf.cardid as 卡号,member.mname as 姓名,jcxf.djbh as 单据号,xmname as 项目名称,jcxf.xfcs as 消费次数,jcxf.riqi as 消费日期 ,bz as 体重 ,bz1 as 备注 ,case(bs) when '0' then '消' when '1' then '退' when '2' then '消' end as 标识 from jcxf  join member on jcxf.cardid=member.mid where cardid='{0}' and '{1}'<=riqi and riqi<='{2}' order by jcxf.id", cardid, kstime, endtime+"23:59:59");
            //MessageBox.Show(sql4);
                SqlDataAdapter sda4 = new SqlDataAdapter(sql4,conn1);
                DataTable dt = new DataTable();
                sda4.Fill(dt);
                dataGridView1.DataSource = dt;
            SqlConnection conn66 = new SqlConnection(constr);
            conn66.Open();
            string sql66 =string.Format("select COUNT(*) from jcxf  where cardid='{0}' and '{1}'<=riqi and riqi<='{2}'", cardid, kstime, endtime+"23:59:59");
            SqlCommand comm66 = new SqlCommand(sql66,conn66);
            lbrenshu.Text = comm66.ExecuteScalar().ToString();
                conn.Close();
                conn1.Close();
            return ds;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(this.txtcardid.Text!=string.Empty && cbxm.Checked==false)
            {
                XtraReport1 xt = new XtraReport1(getdatacardid());
                xt.ShowPreview();
               
            }
            else if (this.txtcardid.Text == string.Empty && cbxm.Checked == true)
            {
                XtraReport1 xt = new XtraReport1(xmidselect());
                xt.ShowPreview();
            }
            else if (txtcardid.Text != string.Empty && cbxm.Checked == true)
            {
                XtraReport1 xt = new XtraReport1(getxmcard());
                xt.ShowPreview();
            }else if(txtcardid.Text==string.Empty && cbxm.Checked==false)
            {
                XtraReport1 xt = new XtraReport1(getxmtime());
                xt.ShowPreview();
            }
        }

        private void dataGridView1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            if (e.RowIndex < dataGridView1.Rows.Count - 1)
            {
                DataGridViewRow dgrSingle = dataGridView1.Rows[e.RowIndex];
                try
                {
                    if (dgrSingle.Cells["标识"].Value.ToString().Contains("退"))
                    {
                        dgrSingle.DefaultCellStyle.ForeColor = Color.Red;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,
        e.RowBounds.Location.Y,
        dataGridView1.RowHeadersWidth - 4,
        e.RowBounds.Height);

            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
            dataGridView1.RowHeadersDefaultCellStyle.Font,
                rectangle,
                dataGridView1.RowHeadersDefaultCellStyle.ForeColor,
                TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Frmtizhong tz = new Frmtizhong();
            chuanzhi.tzcardid = dataGridView1.SelectedCells[1].Value.ToString();
            tz.ShowDialog();
        }
    }
}
